package com.lovo.roomProject.room.dao.impl;

import com.lovo.roomProject.dao.BaseDAO;
import com.lovo.roomProject.pojo.ClaszDO;
import com.lovo.roomProject.pojo.CutPageBean;
import com.lovo.roomProject.pojo.RoomDO;
import com.lovo.roomProject.pojo.StudentDO;
import com.lovo.roomProject.room.dao.RoomDAO;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class RoomDAOImpl extends BaseDAO implements RoomDAO {

    /**
     * 房间显示
     *
     * @return
     */
    @Override
    public List listRoom() {
        this.getConnection();
        String sql = " select r.*,count(s.s_r_id) " +
                " from t_room r left join t_student s on r.r_id=s.s_r_id " +
                " group by r.r_id;";

        List list = new ArrayList();
        try {
            statement = con.prepareStatement(sql);
            rs = statement.executeQuery();

            while (rs.next()) {
                RoomDO room = new RoomDO();
                room.setId(rs.getInt("r_id"));
                room.setAddr(rs.getString("r_addr"));
                room.setCount(rs.getInt("r_count"));
                room.setStatus(rs.getString("r_status"));
                room.setType(rs.getString("r_type"));
                room.setInCount(rs.getInt("count(s.s_r_id)"));

                list.add(room);

            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
        return list;
    }

    @Override
    public List listRoomByType(String type) {

        this.getConnection();
        String sql = "select r.r_id, r.r_addr,r.r_count from t_room r left join t_student s " +
                " on r.r_id=s.s_r_id " +
                " where r.r_type=? " +
                " group by r.r_id,  r.r_addr, r.r_count " +
                " having count(s.s_id)<r.r_count";

        List list = new ArrayList();
        try {
            statement = con.prepareStatement(sql);
            statement.setObject(1, type);
            rs = statement.executeQuery();

            while (rs.next()) {
                RoomDO room = new RoomDO();
                room.setId(rs.getInt("r_id"));
                room.setAddr(rs.getString("r_addr"));
                room.setCount(rs.getInt("r_count"));


                list.add(room);

            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
        return list;
    }

    /**
     * 房间查找和分页
     *
     * @param current
     * @return
     */
    @Override
    public CutPageBean listRoomByPage(int current, String addr, String status, String available, String type) {
        this.getConnection();
        String selectSql = "select r.*,count(s.s_id) ";
        String countSql = "select count(distinct r.r_id) cnt ";
        String fromSql = "from t_room r left join t_student s on r.r_id=s.s_r_id ";
        String groupBySql = "group by r.r_id ";

        String limitSql = " limit ?,?";

        String dynamicSql = " where 1=1 ";

        if (addr != null && !addr.equals("")) {
            dynamicSql += " and r.r_addr like '%" + addr + "%'";
        }
        if (!status.equals("不限")) {
            dynamicSql += " and r.r_status = '" + status + "'";
        }

        if (!type.equals("不限")) {
            dynamicSql += " and r.r_type = '" + type + "'";
        }
        if (!available.equals("不限")) {
            groupBySql += " having  count (s.s_id)<r.r_count";
        }

        CutPageBean cutPageBean = new CutPageBean();
        List list = new ArrayList();

        try {
            statement = con.prepareStatement(selectSql + fromSql + dynamicSql + groupBySql + limitSql);
            statement.setObject(1, (current - 1) * CutPageBean.PAGESIZE);
            statement.setObject(2, CutPageBean.PAGESIZE);
            rs = statement.executeQuery();

            while (rs.next()) {
                RoomDO room = new RoomDO();
                room.setId(rs.getInt("r_id"));
                room.setAddr(rs.getString("r_addr"));
                room.setCount(rs.getInt("r_count"));
                room.setInCount(rs.getInt("count(s.s_id)"));
                room.setStatus(rs.getString("r_status"));
                room.setType(rs.getString("r_type"));


                list.add(room);

            }
            cutPageBean.setList(list);
            statement = con.prepareStatement(countSql + fromSql + dynamicSql);
            rs = statement.executeQuery();
            rs.next();
            cutPageBean.setCount(rs.getInt("cnt"));


        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close(con, statement, rs);
        }


        return cutPageBean;
    }


    /**
     * 通过房间ID查询
     *
     * @param id
     * @return
     */
    @Override
    public RoomDO getById(int id) {
        this.getConnection();
        String sql = "select r.*,count(s.s_id) " +
                "from t_room r left join t_student s on r.r_id=s.s_r_id " +
                "where r.r_id=?";
        RoomDO roomDO = new RoomDO();
        try {
            statement = con.prepareStatement(sql);
            statement.setObject(1, id);
            rs = statement.executeQuery();
            if (rs.next()) {
                roomDO.setId(rs.getInt("r_id"));
                roomDO.setAddr(rs.getString("r_addr"));
                roomDO.setCount(rs.getInt("r_count"));
                roomDO.setStatus(rs.getString("r_status"));
                roomDO.setType(rs.getString("r_type"));
                roomDO.setInCount(rs.getInt("count(s.s_id)"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close(con, statement, rs);
        }

        return roomDO;


    }


    /**
     * 添加房间
     *
     * @param roomDO
     */
    @Override
    public void insert(RoomDO roomDO) {
        this.getConnection();
        String sql = "insert into t_room(r_addr,r_count,r_status,r_type) " +
                " VALUES(?,?,?,?)";
        try {
            statement = con.prepareStatement(sql);
            statement.setObject(1, roomDO.getAddr());
            statement.setObject(2, roomDO.getCount());
            statement.setObject(3, roomDO.getStatus());
            statement.setObject(4, roomDO.getType());
            statement.executeUpdate();


        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close(con, statement, rs);
        }


    }


    /**
     * 查询房间地址
     *
     * @param addr
     * @return
     */
    @Override
    public RoomDO getAddr(String addr) {
        this.getConnection();
        RoomDO room = null;

        String sql = "SELECT r_addr FROM t_room WHERE r_addr= ?";
//        List list = new ArrayList();

//
        try {

            statement = con.prepareStatement(sql);
            statement.setObject(1, addr);
            rs = statement.executeQuery();

            if (rs.next()) {
                room = new RoomDO();

                room.setAddr(rs.getString("r_addr"));


            }


        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close(con, statement, rs);
        }

        return room;
    }

    /**
     * 删除房间
     *
     * @param id
     * @return
     */
    @Override
    public void delete(int id) {
        this.getConnection();
        String sql = "delete from t_room where r_id=?";

        try {
            statement = con.prepareStatement(sql);
            statement.setObject(1, id);
            statement.executeUpdate();

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close(con, statement);
        }


    }


    /**
     * 查询房间入住情况
     *
     * @param id
     * @return
     */
    @Override
    public int getCount(int id) {
        this.getConnection();
        RoomDO room = null;
        String sql = "select r.*, count(s.s_r_id) from t_room r left join t_student s on r.r_id=s.s_r_id " +
                " group by r.r_id HAVING r.r_id= ? ";

        try {
            statement = con.prepareStatement(sql);
            statement.setObject(1, id);
            rs = statement.executeQuery();


            if (rs.next()) {
                room = new RoomDO();
//
                room.setInCount(rs.getInt("count(s.s_r_id)"));

            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close(con, statement, rs);
        }


        return room.getInCount();
    }

    @Override
    public List listStudentById(int id) {
        this.getConnection();
        String sql = "select s.s_name,s.s_phone,c.c_id,c.c_name " +
                " from t_room r left join t_student s on r.r_id=s.s_r_id " +
                " left join t_class c on s.s_c_id=c.c_id " +
                " where r.r_id=?";

        List list = new ArrayList();
        try {
            statement = con.prepareStatement(sql);
            statement.setObject(1, id);
            rs = statement.executeQuery();
            while (rs.next()) {
                StudentDO studentDO = new StudentDO();
                studentDO.setName(rs.getString("s_name"));
                studentDO.setPhone(rs.getString("s_phone"));

                ClaszDO claszDO = new ClaszDO();
                claszDO.setId(rs.getInt("c_id"));
                claszDO.setName(rs.getString("c_name"));

                studentDO.setClasz(claszDO);
                list.add(studentDO);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close(con, statement, rs);
        }
        return list;


    }

    @Override
    public void update(RoomDO roomDO) {
        this.getConnection();
        String sql = "update t_room set r_status=? where r_id=?";
        try {
            statement = con.prepareStatement(sql);
            statement.setObject(1, roomDO.getStatus());
            statement.setObject(2, roomDO.getId());
            statement.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close(con, statement);
        }


    }


//    public static void main(String[] args) {
//        RoomDAO roomDAO = new RoomDAOImpl();
//
//
//        System.out.println(roomDAO.getCount(16));
//
//
//    }
}




